package com.api.gbhg.liuwenbo.dao;

import com.api.gbhg.liwenjun.VersionFlowUtil;
import weaver.conn.RecordSet;
import weaver.file.Prop;
import weaver.general.Util;

import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

/**
 * @Description
 * @Author
 * @Date
 */
public class BudgetSectionItemListDao {
    /**
     * 获取中央核批渠道
     * @param id
     * @return
     */
    public String getzyhpqd(String id){
        RecordSet rs = new RecordSet();
        String sql = "select id,srzx,sjzxsr,szgnkm from uf_zyczbksr_xj where id = "+id+"";
        rs.executeQuery(sql);
        rs.next();
        if (!Util.null2String(rs.getString("sjzxsr")).isEmpty()){
            return getzyhpqd(rs.getString("sjzxsr"))+"-"+rs.getString("srzx");
        }
        return rs.getString("srzx");
    }

    /**
     * 获取科目代码
     * @param id
     * @return
     */
    public String getKmdm(String id){
        RecordSet rs = new RecordSet();
        String sql2 = " select kmdm from uf_zyczbksr_km where id = "+id+" ";
        rs.executeQuery(sql2);
        rs.next();
        return rs.getString("kmdm");
    }

    /**
     * 获取科目代码
     * @param id
     * @return
     */
    public String getKmdmByXjId(String id){
        RecordSet rs = new RecordSet();
        String sql2 = " select kmdm from uf_zyczbksr_km where id = (select szgnkm from uf_zyczbksr_xj where id = "+id+") ";
        rs.executeQuery(sql2);
        rs.next();
        return rs.getString("kmdm");
    }

    /**
     * 获取细化明细表中合计
     * @param formid
     * @param mainid
     * @return
     */
    public String getDetailTableXhHj(int formid,String mainid)throws Exception {
        RecordSet rs = new RecordSet();
        int formid18 = VersionFlowUtil.getFlowTableLableId(Prop.getPropValue("FormationWorkflowid", "BZ18Workflowid"));
        if (formid != formid18){
            rs.executeQuery("SELECT d.tablename FROM workflow_billdetailtable d where d.billid=" + formid + " order by tablename desc");
            rs.next();
            rs.next();
            rs.next();
            rs.next();

            String tablename = rs.getString("tablename");
            String hj = getDetailFieldHj(tablename);
            rs.executeQuery("select SUM("+hj+") hj from "+rs.getString("tablename")+" where mainid = "+mainid+"");
        }else {
            rs.executeQuery("SELECT d.tablename FROM workflow_billdetailtable d where d.billid=" + formid + " order by tablename desc");
            rs.next();
            rs.next();
            rs.next();
            rs.next();
            rs.next();
            rs.next();
            rs.next();
            rs.executeQuery("select SUM(sheet4_detail2_field3) hj from "+rs.getString("tablename")+" where mainid = "+mainid+"");
        }
        rs.next();
        return rs.getString("hj").equals("")?"0":rs.getString("hj");
    }

    /**
     * 获取所有明细，去除后面4个明细
     * @param formid
     * @return
     */
    public List<String> getDetailTables1(int formid){
        RecordSet rs = new RecordSet();
        String sql = "SELECT TOP 5 tablename FROM workflow_billdetailtable \n" +
                " WHERE workflow_billdetailtable.id\n" +
                "  NOT IN\n" +
                " (SELECT TOP 4 id FROM workflow_billdetailtable where billid="+formid+"  order by tablename desc )\n" +
                " and billid="+formid+"  order by tablename ";
        rs.executeQuery(sql);
        List<String> list = new ArrayList<>();
        while (rs.next()){
            list.add(rs.getString("tablename"));
        }
        return list;
    }

    /**
     * 取表单明细合计字段名
     * @param tableName
     * @return
     */
    public String getDetailFieldHj(String tableName){
        RecordSet rs = new RecordSet();
        String sql = "SELECT COUNT(id) count FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = '"+tableName+"') AND name = 'hj' ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("count")==1?"hj":"hjwy";
    }

    /**
     * 拼接明细表求和sql (zfcgfwy)
     * @param list
     * @param mainid
     * @return
     */
    public String joinDtSumSql(List<String> list,String mainid){
        StringBuilder field = new StringBuilder();
        StringBuilder dTab = new StringBuilder();

        if (list.size() == 1){
            field.append("sum(zfcgfwy) zfcgfwy");
            dTab.append(list.get(0));
            return "select "+field.toString()+" from "+dTab.toString()+" where mainid ="+mainid+" ";
        }else{
            for (int i = 0; i < list.size(); i++) {
                if (i==0){
                    field.append("sum(isnull(a"+i+".zfcgfwy,0)+");
                    dTab.append(list.get(i)+" a"+i+" full join ");
                }else{
                    field.append("isnull(a"+i+".zfcgfwy,0)+");
                    dTab.append(list.get(i)+" a"+i+" on a"+(i-1)+".mainid =a"+i+".mainid full join ");
                }

            }
        }
        return "select "+field.substring(0,field.length()-1)+") from "+dTab.substring(0,dTab.length() - 10)+" where a0.mainid ="+mainid+" ";
    }

    /**
     * 获取明细表中政府购买服务/政府采购
     * @param formid
     * @param mainid
     * @return
     */
    public Map<String, Object> getDetailTableZfGmZe(int formid, String mainid)throws Exception {
        RecordSet rs = new RecordSet();
        Map<String,Object> resultMap = new HashMap<>();
        List<String> list = new ArrayList<>();
        rs.executeQuery("SELECT d.tablename FROM workflow_billdetailtable d where d.billid=" + formid + " order by tablename desc");
        rs.next();
        rs.next();
        rs.next();
        rs.next();
        while (rs.next()){
            list.add(rs.getString("tablename"));
        }

        //政府购买服务金额总额
        BigDecimal zfCgFwCount = new BigDecimal(0);
        //政府采购各类型金额统计总额
        BigDecimal hwGzCount = new BigDecimal(0);  //0
        BigDecimal gcCount = new BigDecimal(0);    //1
        BigDecimal fWCount = new BigDecimal(0);    //2
        BigDecimal xeCgCount = new BigDecimal(0);  //3

        for (String s : list) {
            //取表单明细政府购买服务字段名
            String zfCgFw = getDetailFieldZfCgFw(s);
            //取表单明细政府采购金额字段名
            String je = getDetailFieldJe(s);
            List<Map<String,Object>> mapList = new ArrayList<>();
            boolean b1 = rs.executeQuery("select " + zfCgFw + "," + je + ",lx from " + s + " where mainid = " + mainid + "");
            if (b1){
                while (rs.next()){
                    Map<String,Object> map = new HashMap<>();
                    String zf = rs.getString(zfCgFw);
                    if (!zf.equals("")){
                        zfCgFwCount = zfCgFwCount.add(new BigDecimal(zf));
                    }
                    String lx = rs.getString("lx");
                    if (!lx.equals("")){
                        map.put("je",rs.getString(je));
                        map.put("lx",rs.getString("lx"));
                        mapList.add(map);
                    }
                }

                if (!mapList.isEmpty()){
                    //计算政府采购金额
                    Map<Object, List<Map<String, Object>>> lxMap = mapList.stream().collect(Collectors.groupingBy(b -> b.get("lx")));
                    Iterator<Map.Entry<Object, List<Map<String, Object>>>> lxIta = lxMap.entrySet().iterator();
                    while (lxIta.hasNext()){
                        Map.Entry<Object, List<Map<String, Object>>> lx = lxIta.next();
                        String key = String.valueOf(lx.getKey());
                        List<Map<String, Object>> lxValue = lx.getValue();
                        AtomicReference<BigDecimal> jeCount = new AtomicReference<>(new BigDecimal(0));
                        lxValue.forEach(x->{
                            String jes = String.valueOf(x.get("je"));
                            if (!jes.equals("")){
                                jeCount.set(jeCount.get().add(new BigDecimal(jes)));
                            }
                        });
                        switch (key){
                            case "0":
                                hwGzCount = hwGzCount.add(new BigDecimal(String.valueOf(jeCount)));
                                break;
                            case "1":
                                gcCount = gcCount.add(new BigDecimal(String.valueOf(jeCount)));
                                break;
                            case "2":
                                fWCount = fWCount.add(new BigDecimal(String.valueOf(jeCount)));
                                break;
                            case "3":
                                xeCgCount = xeCgCount.add(new BigDecimal(String.valueOf(jeCount)));
                                break;
                        }
                    }
                }
            }
        }

        //将结果存入集合返回
        resultMap.put("zfcgfwy",zfCgFwCount);
        resultMap.put("hwgz",hwGzCount);
        resultMap.put("gc",gcCount);
        resultMap.put("fw",fWCount);
        resultMap.put("xecg",xeCgCount);


        return resultMap;
    }

    /**
     * 取表单明细政府采购金额字段名
     * @param s
     * @return
     */
    private String getDetailFieldJe(String s) {
        RecordSet rs = new RecordSet();
        String sql = "SELECT COUNT(id) count FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = '"+s+"') AND name = 'je' ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("count")==1?"je":"jewy";
    }

    /**
     * 取表单明细政府购买服务字段名
     * @param s
     * @return
     */
    private String getDetailFieldZfCgFw(String s) {
        RecordSet rs = new RecordSet();
        String sql = "SELECT COUNT(id) count FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = '"+s+"') AND name = 'zfcgfwy' ";
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("count")==1?"zfcgfwy":"zfgmfwy";
    }

    /**
     * 校验requestid是否存在经费版本表
     * @param requestid
     * @return
     */
    public boolean IsJinfeiVersion(String requestid){
        RecordSet rs = new RecordSet();
        String sql = "select count(*) cnt from uf_jingfei_version where requestid = "+requestid+"";
        rs.executeQuery(sql);
        rs.next();
        if (rs.getInt("cnt")>0){
            return true;
        }else{
            return false;
        }
    }

    /**
     * 校验requestid获取uf_jingfei_version的项目名称
     * @param requestid
     * @return
     */
    public String getJingFeiXmmc(String requestid){
        RecordSet rs = new RecordSet();
        String sql = "select xmmin,sfwzjxm from uf_jingfei_version where requestid = "+requestid+"";
        rs.executeQuery(sql);
        rs.next();
        String xmmin = rs.getString("xmmin");
        String sfwzjxm = rs.getString("sfwzjxm");
        return xmmin+"(是否追加:"+(sfwzjxm.isEmpty() || sfwzjxm.equals("0")?"否":"是")+")";
    }
}
